import mysql from 'mysql2/promise';
import logger from '../utils/logger.js';
import { promises as fs } from 'fs';
import path from 'path';
import 'dotenv/config';
import { fileURLToPath } from "url";
import { dirname } from "path";
const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);


/* *******************
 * 配置管理
 ******************* */
async function generateTableConfigFromSQL() {
  try {
    const sqlPath = path.join(__dirname, 'init.sql');
    const content = await fs.readFile(sqlPath, 'utf8');
    
    // 清理SQL内容
    const cleanedContent = content
      .replace(/--.*$/gm, '') // 移除单行注释
      .replace(/\/\*[\s\S]*?\*\//g, ''); // 移除多行注释
    
    const tables = {};
    
    // 匹配CREATE TABLE语句
    const tableRegex = /CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`(\w+)`\s*\(([\s\S]*?)\)\s*(?:ENGINE|$)/gi;
    let match;
    
    while ((match = tableRegex.exec(cleanedContent)) !== null) {
      const tableName = match[1];
      const tableContent = match[2];
      
      // 解析列定义
      const columns = [];
      const indexes = [];
      const columnTypes = {};
      const notNullColumns = [];
      const defaultValues = {};
      
      // 分割并清理列定义
      const columnLines = tableContent
        .split(',')
        .map(line => line.trim())
        .filter(line => line.length > 0);
      
      for (const line of columnLines) {
        // 匹配列定义
        const columnMatch = line.match(/^`(\w+)`\s+([\w()]+)(\s+.*)?$/);
        if (columnMatch) {
          const columnName = columnMatch[1];
          const columnType = columnMatch[2].toLowerCase();
          columns.push(columnName);
          
          // 存储所有列的类型信息
          columnTypes[columnName] = columnType;
          
          // 检查是否有NOT NULL约束
          const notNull = line.toLowerCase().includes('not null');
          if (notNull) {
            notNullColumns.push(columnName);
          }
          
          // 检查默认值
          const defaultMatch = /\sDEFAULT\s+([^\s,]+)/i.exec(line);
          if (defaultMatch) {
            defaultValues[columnName] = defaultMatch[1];
          }
        }
        
        // 匹配主键定义
        const primaryKeyMatch = line.match(/PRIMARY\s+KEY\s*\(`[^`]+`\)/i);
        if (primaryKeyMatch) {
          indexes.push('PRIMARY');
          continue;
        }
        
        // 匹配其他索引定义
        const indexMatch = line.match(/(?:UNIQUE\s+)?(?:KEY|INDEX)\s+`(\w+)`/i);
        if (indexMatch && indexMatch[1]) {
          indexes.push(indexMatch[1]);
        }
      }
      
      // 检查是否需要初始化数据
      const hasInitData = cleanedContent.includes(`INSERT INTO \`${tableName}\``);
      
      tables[tableName] = {
        columns,
        indexes,
        columnTypes: Object.keys(columnTypes).length > 0 ? columnTypes : undefined,
        notNullColumns: notNullColumns.length > 0 ? notNullColumns : undefined,
        defaultValues: Object.keys(defaultValues).length > 0 ? defaultValues : undefined,
        initData: hasInitData
      };
      
      logger.debug(`解析表 ${tableName} 配置:`, {
        columnsCount: columns.length,
        indexesCount: indexes.length,
        columnTypesCount: Object.keys(columnTypes).length,
        notNullColumnsCount: notNullColumns.length,
        defaultValuesCount: Object.keys(defaultValues).length,
        hasInitData
      });
    }
    
    return tables;
  } catch (error) {
    logger.error('从SQL文件生成表配置失败:', error);
    throw error;
  }
}

async function generateDatabaseConfig() {
  try {
    const sqlPath = path.join(__dirname, 'init.sql');
    const content = await fs.readFile(sqlPath, 'utf8');
    
    // 匹配数据库创建语句中的字符集和排序规则
    const dbConfigRegex = /CREATE\s+DATABASE\s+(?:IF\s+NOT\s+EXISTS\s+)?[\w_]+\s*(?:CHARACTER\s+SET\s+([^\s]+))?\s*(?:COLLATE\s+([^\s;]+))?/i;
    const match = content.match(dbConfigRegex);
    
    return {
      charset: match?.[1]?.toLowerCase() || 'utf8mb4',
      collation: match?.[2]?.toLowerCase() || 'utf8mb4_unicode_ci'
    };
  } catch (error) {
    logger.error('解析数据库配置失败:', error);
    return {
      charset: 'utf8mb4',
      collation: 'utf8mb4_unicode_ci'
    };
  }
}

// 初始化TABLE_CONFIG
let TABLE_CONFIG = {};

// 从SQL文件解析表依赖关系
async function generateTableDependenciesFromSQL() {
  try {
    const sqlPath = path.join(__dirname, 'init.sql');
    const content = await fs.readFile(sqlPath, 'utf8');
    
    // 清理SQL内容
    const cleanedContent = content
      .replace(/--.*$/gm, '') // 移除单行注释
      .replace(/\/\*[\s\S]*?\*\//g, ''); // 移除多行注释
    
    const dependencies = {};
    
    // 匹配外键约束
    const foreignKeyRegex = /CONSTRAINT\s+`[^`]*`\s+FOREIGN\s+KEY\s+\(`([^`]+)`\)\s+REFERENCES\s+`([^`]+)`/gi;
    let match;
    
    while ((match = foreignKeyRegex.exec(cleanedContent)) !== null) {
      const childTable = getTableNameFromForeignKeyDefinition(cleanedContent, match.index);
      const parentTable = match[2];
      
      if (childTable && parentTable) {
        if (!dependencies[childTable]) {
          dependencies[childTable] = [];
        }
        
        // 避免重复添加依赖
        if (!dependencies[childTable].includes(parentTable)) {
          dependencies[childTable].push(parentTable);
        }
      }
    }
    
    logger.debug('从SQL文件解析表依赖关系:', dependencies);
    return dependencies;
  } catch (error) {
    logger.error('从SQL文件解析表依赖关系失败:', error);
    return {};
  }
}

// 从外键定义中获取表名
function getTableNameFromForeignKeyDefinition(sqlContent, constraintPosition) {
  // 向前查找CREATE TABLE语句
  const createTableRegex = /CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`(\w+)`/gi;
  let tableMatch;
  let lastTableName = null;
  
  // 重置正则表达式的lastIndex
  createTableRegex.lastIndex = 0;
  
  while ((tableMatch = createTableRegex.exec(sqlContent)) !== null) {
    if (tableMatch.index < constraintPosition) {
      lastTableName = tableMatch[1];
    } else {
      break;
    }
  }
  
  return lastTableName;
}

// 表依赖关系配置 - 将在初始化时动态生成
let TABLE_DEPENDENCIES = {};

// 拓扑排序函数
function topologicalSort(tables, dependencies) {
  const visited = {};
  const temp = {};
  const result = [];
  
  function visit(table) {
    if (temp[table]) {
      throw new Error(`检测到循环依赖: ${table}`);
    }
    if (!visited[table]) {
      temp[table] = true;
      
      // 先处理依赖表
      if (dependencies[table]) {
        for (const dep of dependencies[table]) {
          visit(dep);
        }
      }
      
      visited[table] = true;
      delete temp[table];
      result.push(table);
    }
  }
  
  for (const table of tables) {
    visit(table);
  }
  
  return result;
}

// 使用IIFE立即执行异步函数来初始化配置
(async () => {
  try {
    // 加载表结构配置
    TABLE_CONFIG = await generateTableConfigFromSQL();
    
    // 加载表依赖关系
    TABLE_DEPENDENCIES = await generateTableDependenciesFromSQL();
    
    // 预加载SQL语句
    await loadSQLFile();
  } catch (error) {
    logger.error('初始化配置失败:', error);
    throw error;
  }
})();

/* *******************
 * SQL文件预加载
 ******************* */
let sqlStatements = [];

async function loadSQLFile() {
  try {
    const sqlPath = path.join(__dirname, 'init.sql');
    const content = await fs.readFile(sqlPath, 'utf8');
    
    // 改进SQL语句解析逻辑
    // 1. 移除注释
    let cleanedContent = content
      .replace(/--.*$/gm, '') // 移除单行注释
      .replace(/\/\*[\s\S]*?\*\//g, ''); // 移除多行注释
      
    // 2. 处理USE语句 - 我们将自己处理数据库选择
    cleanedContent = cleanedContent.replace(/USE\s+.*?;/gi, '');
    
    // 3. 分割SQL语句并过滤空语句
    sqlStatements = cleanedContent
      .split(';')
      .map(stmt => stmt.trim())
      .filter(stmt => stmt.length > 0);
    
    // 4. 调试信息
    if (sqlStatements.length === 0) {
      logger.warn('未找到任何SQL语句，请检查init.sql文件');
    }
  } catch (error) {
    logger.error('SQL文件加载失败:', error);
    throw error;
  }
}

/* *******************
 * 核心验证逻辑
 ******************* */
async function checkTableStructure(connection, tableName) {
  const config = TABLE_CONFIG[tableName];
  
  try {
    // 检查表是否存在
    const [tables] = await connection.query(
      `SELECT TABLE_NAME 
       FROM information_schema.TABLES 
       WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?`,
      [process.env.DB_NAME, tableName]
    );
    
    if (tables.length === 0) {
      logger.info(`表 ${tableName} 不存在，需要创建`);
      return true;
    }
    
    const [columns] = await connection.query(
      `SELECT COLUMN_NAME, COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT, COLUMN_KEY, EXTRA 
       FROM information_schema.COLUMNS 
       WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?`,
      [process.env.DB_NAME, tableName]
    );

    // 列验证
    const missingColumns = config.columns.filter(
      col => !columns.some(dbCol => dbCol.COLUMN_NAME === col)
    );
    
    // 检查是否有列被删除
    const extraColumns = columns.filter(
      dbCol => !config.columns.some(col => col === dbCol.COLUMN_NAME)
    );

    if (missingColumns.length || extraColumns.length) {
      let message = `表 ${tableName} 结构不一致: `;
      if (missingColumns.length) {
        message += `数据库缺少列: ${missingColumns.join(', ')}`;
      }
      if (extraColumns.length) {
        if (missingColumns.length) message += ', ';
        message += `数据库多余列: ${extraColumns.map(c => c.COLUMN_NAME).join(', ')}`;
      }
      logger.info(message);
      return true;
    }

    // 列属性验证
    let hasTypeIssue = false;
    
    // 遍历所有列进行详细验证
    for (const col of config.columns) {
      const dbColumn = columns.find(c => c.COLUMN_NAME === col);
      if (!dbColumn) continue; // 已在上面的缺少列检查中处理
      
      // 1. 验证列类型
      if (config.columnTypes && config.columnTypes[col]) {
        const dbType = dbColumn.COLUMN_TYPE.toLowerCase();
        const expectedType = config.columnTypes[col].toLowerCase();
        
        // 基本类型匹配（如varchar vs tinyint）
        const dbTypeBase = dbType.split('(')[0];
        const expectedTypeBase = expectedType.split('(')[0];
        
        if (dbTypeBase !== expectedTypeBase) {
          logger.info(
            `表 ${tableName} 列 ${col} 类型不匹配: 期望 ${expectedType}, 实际 ${dbType}`
          );
          hasTypeIssue = true;
        } else {
          // 2. 验证类型长度/精度
          // VARCHAR/CHAR类型长度验证
          const varcharMatch = /(varchar|char)\((\d+)\)/i.exec(expectedType);
          if (varcharMatch) {
            const expectedLength = parseInt(varcharMatch[2]);
            const dbLength = dbColumn.CHARACTER_MAXIMUM_LENGTH;
            
            if (dbLength !== expectedLength) {
              hasTypeIssue = true;
            }
          }
          
          // TINYINT/INT类型精度验证
          const intMatch = /(tinyint|int|bigint|smallint|mediumint)\((\d+)\)/i.exec(expectedType);
          if (intMatch) {
            const expectedPrecision = parseInt(intMatch[2]);
            const dbPrecisionMatch = /(tinyint|int|bigint|smallint|mediumint)\((\d+)\)/i.exec(dbType);
            const dbPrecision = dbPrecisionMatch ? parseInt(dbPrecisionMatch[2]) : null;
            
            if (dbPrecision !== expectedPrecision) {
              hasTypeIssue = true;
            }
          }
        }
      }
      
      // 3. 验证NULL约束
      if (config.notNullColumns && config.notNullColumns.includes(col)) {
        if (dbColumn.IS_NULLABLE === 'YES') {
          hasTypeIssue = true;
        }
      } else if (dbColumn.IS_NULLABLE === 'NO' && (!config.notNullColumns || !config.notNullColumns.includes(col))) {
        // 检查是否应该允许NULL但实际不允许
        // 主键列和自增列通常是NOT NULL的，所以不需要报错
        const isPrimaryKey = dbColumn.COLUMN_KEY === 'PRI';
        const isAutoIncrement = dbColumn.EXTRA && dbColumn.EXTRA.includes('auto_increment');
        
        // 如果不是主键或自增列，才报错
        if (!isPrimaryKey && !isAutoIncrement) {
          hasTypeIssue = true;
        }
      }
      
      // 4. 验证默认值
      if (config.defaultValues && config.defaultValues[col] !== undefined) {
        const expectedDefault = config.defaultValues[col];
        const dbDefault = dbColumn.COLUMN_DEFAULT;
        
        // 特殊处理NULL默认值
        if (expectedDefault === 'NULL' && dbDefault === null) {
          // 这是正确的，跳过
        } else if (dbDefault === null && expectedDefault !== null) {
          hasTypeIssue = true;
        } else if (dbDefault !== null && String(dbDefault) !== String(expectedDefault)) {
          hasTypeIssue = true;
        }
      } else if (dbColumn.COLUMN_DEFAULT !== null && (!config.defaultValues || config.defaultValues[col] === undefined)) {
        // 检查是否应该没有默认值但实际有默认值
        // 自增列、主键列和常见的状态字段通常有默认值，所以不需要报错
        const isAutoIncrement = dbColumn.EXTRA && dbColumn.EXTRA.includes('auto_increment');
        const isPrimaryKey = dbColumn.COLUMN_KEY === 'PRI';
        const isCommonDefaultField = [
          'status', 'is_deleted', 'created_at', 'updated_at', 'sort', 'parent_id'
        ].includes(col.toLowerCase());
        
        if (!isAutoIncrement && !isPrimaryKey && !isCommonDefaultField) {
          hasTypeIssue = true;
        }
      }
    }
    
    if (hasTypeIssue) return true;

    // 索引验证
    const [indexes] = await connection.query(
      `SELECT INDEX_NAME 
       FROM information_schema.STATISTICS 
       WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?`,
      [process.env.DB_NAME, tableName]
    );

    if (!config.indexes) {
      return false; // 如果没有定义索引要求，则认为索引验证通过
    }

    const requiredIndexes = config.indexes.map(idx => idx.toLowerCase());
    const existingIndexes = indexes.map(idx => idx.INDEX_NAME.toLowerCase());

    // 特殊处理主键索引
    if (requiredIndexes.includes('primary') && !existingIndexes.includes('primary')) {
      return true;
    }

    const missingIndexes = requiredIndexes.filter(
      idx => idx !== 'primary' && !existingIndexes.includes(idx)
    );
    
    if (missingIndexes.length > 0) {
      return true;
    }
    
    return false;
  } catch (error) {
    logger.error(`验证表 ${tableName} 结构时出错:`, error);
    return true; // 出错时保守处理，认为需要重建
  }
}

/* *******************
 * 数据库验证
 ******************* */
async function validateDatabaseStructure(connection) {
  try {
    const tablesToRebuild = [];

    // 获取SQL文件中的数据库配置
    const expectedConfig = await generateDatabaseConfig();
    
    // 数据库级验证
    const [databases] = await connection.query(
      `SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
       FROM information_schema.SCHEMATA 
       WHERE SCHEMA_NAME = ?`,
      [process.env.DB_NAME]
    );

    if (databases.length === 0) {
      logger.warn('数据库不存在，需要创建');
      return {
        isValid: false,
        tablesToRebuild: Object.keys(TABLE_CONFIG),
        charset: expectedConfig.charset,
        collation: expectedConfig.collation
      };
    }
    
    // 验证字符集和排序规则
    const actualCharset = databases[0].DEFAULT_CHARACTER_SET_NAME.toLowerCase();
    const actualCollation = databases[0].DEFAULT_COLLATION_NAME.toLowerCase();
    
    logger.info(`数据库配置: 期望 charset=${expectedConfig.charset}, collation=${expectedConfig.collation}; 实际 charset=${actualCharset}, collation=${actualCollation}`);
    
    if (actualCharset !== expectedConfig.charset || actualCollation !== expectedConfig.collation) {
      logger.warn('数据库字符集或排序规则不匹配，需要重建数据库');
      return {
        isValid: false,
        tablesToRebuild: Object.keys(TABLE_CONFIG),
        charset: expectedConfig.charset,
        collation: expectedConfig.collation
      };
    }
    
    // 表级验证
    for (const table of Object.keys(TABLE_CONFIG)) {
      const needsRebuild = await checkTableStructure(connection, table);
      if (needsRebuild) {
        tablesToRebuild.push(table);
      }
    }

    const isValid = tablesToRebuild.length === 0;
    if (isValid) {
      logger.info('所有表结构验证通过，无需重建');
    } else {
      logger.warn(`需要重建 ${tablesToRebuild.length} 个表: ${tablesToRebuild.join(', ')}`);
    }
    
    return { isValid, tablesToRebuild, charset: expectedConfig.charset, collation: expectedConfig.collation };
  } catch (error) {
    logger.error('验证数据库结构时出错:', error);
    return { 
      isValid: false, 
      tablesToRebuild: [],
      charset: 'utf8mb4',
      collation: 'utf8mb4_unicode_ci'
    };
  }
}

/* *******************
 * 重建逻辑
 ******************* */
async function rebuildTable(connection, tableName) {
  const safeTableName = mysql.escapeId(tableName);

  try {
    // 禁用外键约束检查
    await connection.query('SET FOREIGN_KEY_CHECKS = 0');

    // 删除表
    await connection.query(`DROP TABLE IF EXISTS ${safeTableName}`);

    // 创建表 - 修改查找逻辑以匹配实际SQL格式
    const createStmt = sqlStatements.find(stmt => {
      const lowerStmt = stmt.trim().toLowerCase();
      return (
        lowerStmt.includes(`create table`) && 
        (lowerStmt.includes(`\`${tableName.toLowerCase()}\``) || 
        lowerStmt.includes(`${tableName.toLowerCase()}`))
      );
    });
    
    if (!createStmt) {
      logger.error(`未找到 ${tableName} 的建表语句，可用的SQL语句：`, 
        sqlStatements.map(s => s.substring(0, 50) + '...').join('\n'));
      throw new Error(`未找到 ${tableName} 的建表语句`);
    }
    
    await connection.query(createStmt);
    logger.info(`已重建表 ${tableName}`);

    // 重新启用外键约束检查
    await connection.query('SET FOREIGN_KEY_CHECKS = 1');
  } catch (error) {
    // 确保在出错时也重新启用外键约束检查
    await connection.query('SET FOREIGN_KEY_CHECKS = 1');
    logger.error(`重建表 ${tableName} 失败:`, error);
    throw error;
  }
}

/* *******************
 * 初始化数据
 ******************* */
// 从INSERT语句中提取表名
function extractTableNameFromInsert(insertStmt) {
  const match = insertStmt.match(/INSERT\s+INTO\s+(?:`([^`]+)`|([^\s(]+))/i);
  return match ? (match[1] || match[2]) : null;
}

// 从INSERT语句中提取主键字段和值
async function shouldSkipInsert(connection, tableName, insertStmt) {
  try {
    // 获取表的主键信息
    const [primaryKeyInfo] = await connection.query(
      `SELECT COLUMN_NAME 
       FROM information_schema.KEY_COLUMN_USAGE 
       WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND CONSTRAINT_NAME = 'PRIMARY'`,
      [process.env.DB_NAME, tableName]
    );
    
    if (primaryKeyInfo.length === 0) {
      return false; // 没有主键，不跳过
    }
    
    const primaryKeyColumn = primaryKeyInfo[0].COLUMN_NAME;
    
    // 从INSERT语句中提取列名
    const columnsMatch = insertStmt.match(/INSERT\s+INTO\s+(?:`[^`]+`|[^\s(]+)\s*\(([^)]+)\)/i);
    if (!columnsMatch) {
      return false; // 无法解析列名，不跳过
    }
    
    const columns = columnsMatch[1].split(',').map(col => col.trim().replace(/`/g, ''));
    const primaryKeyIndex = columns.findIndex(col => col.toLowerCase() === primaryKeyColumn.toLowerCase());
    
    if (primaryKeyIndex === -1) {
      return false; // 插入语句中没有主键列，不跳过
    }
    
    // 从VALUES子句中提取值
    const valuesMatch = insertStmt.match(/VALUES\s*\(([^)]+)\)/i);
    if (!valuesMatch) {
      return false; // 无法解析值，不跳过
    }
    
    const values = valuesMatch[1].split(',').map(val => val.trim());
    const primaryKeyValue = values[primaryKeyIndex].replace(/'/g, '');
    
    // 检查记录是否已存在
    const [existingRecord] = await connection.query(
      `SELECT ${mysql.escapeId(primaryKeyColumn)} FROM ${mysql.escapeId(tableName)} WHERE ${mysql.escapeId(primaryKeyColumn)} = ?`,
      [primaryKeyValue]
    );
    
    return existingRecord.length > 0;
  } catch (error) {
    logger.warn(`检查是否应跳过插入时出错: ${error.message}`);
    return false; // 出错时不跳过
  }
}

async function initializeData(connection) {
  try {
    // 禁用外键约束检查
    await connection.query('SET FOREIGN_KEY_CHECKS = 0');
    
    // 查找所有初始化数据的SQL语句
    const insertStatements = sqlStatements.filter(stmt => 
      stmt.trim().toLowerCase().startsWith('insert into')
    );
    
    if (insertStatements.length === 0) {
      logger.warn('未找到初始化数据的SQL语句');
      return;
    }
    
    // 按表分组INSERT语句
    const insertsByTable = {};
    for (const stmt of insertStatements) {
      const tableName = extractTableNameFromInsert(stmt);
      if (tableName) {
        if (!insertsByTable[tableName]) {
          insertsByTable[tableName] = [];
        }
        insertsByTable[tableName].push(stmt);
      }
    }
    
    // 获取所有需要初始化数据的表
    const tablesWithData = Object.keys(insertsByTable);
    
    // 使用拓扑排序确定初始化顺序
    const sortedTables = topologicalSort(tablesWithData, TABLE_DEPENDENCIES);

    
    // 按顺序初始化数据
    for (const table of sortedTables) {
      const tableInserts = insertsByTable[table];
      
      if (tableInserts && tableInserts.length > 0) {
        // 检查表中是否已有数据
        const [rows] = await connection.query(`SELECT COUNT(*) as count FROM ${mysql.escapeId(table)}`);
        
        // 执行插入语句
        for (const insertStmt of tableInserts) {
          try {
            // 检查是否应该跳过此插入
            const skipInsert = await shouldSkipInsert(connection, table, insertStmt);
            
            if (!skipInsert) {
              await connection.query(insertStmt);
            }
          } catch (err) {
            logger.warn(`插入${table}表数据时出现警告: ${err.message}`);
            // 继续处理下一条数据
            continue;
          }
        }

      }
    }
    
    // 重新启用外键约束检查
    await connection.query('SET FOREIGN_KEY_CHECKS = 1');
    logger.info('所有表数据初始化完成');
  } catch (error) {
    // 确保在出错时也重新启用外键约束检查
    await connection.query('SET FOREIGN_KEY_CHECKS = 1');
    logger.error('初始化数据失败:', error);
    throw error;
  }
}

/* *******************
 * 主流程
 ******************* */
async function initDatabase() {
  let connection;

  try {
    logger.info('开始数据库初始化...');

    // 创建基础连接
    logger.info('正在连接数据库服务器...');
    connection = await mysql.createConnection({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      port: process.env.DB_PORT,
    });
    logger.info('数据库服务器连接成功');

    // 验证结构
    logger.info('验证数据库结构...');
    const { isValid, tablesToRebuild, charset, collation } = await validateDatabaseStructure(connection);

    // 创建数据库（如果需要）
    logger.info('检查数据库是否存在...');
    const [databases] = await connection.query(
      `SELECT SCHEMA_NAME 
       FROM information_schema.SCHEMATA 
       WHERE SCHEMA_NAME = ?`,
      [process.env.DB_NAME]
    );

    let isNewDatabase = false;
    if (databases.length === 0) {
      logger.info(`数据库 ${process.env.DB_NAME} 不存在，正在创建...`);
      await connection.query(
        `CREATE DATABASE ${mysql.escapeId(process.env.DB_NAME)} 
         CHARACTER SET ${charset} 
         COLLATE ${collation}`
      );
      logger.info(`数据库 ${process.env.DB_NAME} 创建成功`);
      isNewDatabase = true;
    } else if (!isValid) {
      // 如果数据库存在但配置不匹配，重建数据库
      logger.info(`重建数据库 ${process.env.DB_NAME} 使用新的字符集和排序规则...`);
      await connection.query(`DROP DATABASE ${mysql.escapeId(process.env.DB_NAME)}`);
      await connection.query(
        `CREATE DATABASE ${mysql.escapeId(process.env.DB_NAME)} 
         CHARACTER SET ${charset} 
         COLLATE ${collation}`
      );
      isNewDatabase = true;
    }

    logger.info(`切换到数据库 ${process.env.DB_NAME}`);
    await connection.query(`USE ${mysql.escapeId(process.env.DB_NAME)}`);

    // 如果是新创建的数据库或者结构无效，需要重建表
    if (isNewDatabase) {
      logger.info('新创建的数据库，需要创建所有表并初始化数据');
      
      // 按依赖顺序创建表
      const sortedTables = topologicalSort(Object.keys(TABLE_CONFIG), TABLE_DEPENDENCIES);
      logger.info(`按依赖顺序创建表: ${sortedTables.join(' -> ')}`);
      
      // 禁用外键检查
      await connection.query('SET FOREIGN_KEY_CHECKS = 0');
      
      // 顺序创建表
      for (const table of sortedTables) {
        await rebuildTable(connection, table);
      }
      
      // 重新启用外键检查
      await connection.query('SET FOREIGN_KEY_CHECKS = 1');
      
      // 初始化数据
      await initializeData(connection);
    } else if (!isValid) {
      // 按依赖顺序重建表
      const sortedTables = topologicalSort(tablesToRebuild, TABLE_DEPENDENCIES);
      logger.info(`按依赖顺序重建表: ${sortedTables.join(' -> ')}`);
      
      // 禁用外键检查
      await connection.query('SET FOREIGN_KEY_CHECKS = 0');
      
      // 顺序重建表
      for (const table of sortedTables) {
        await rebuildTable(connection, table);
      }
      
      // 重新启用外键检查
      await connection.query('SET FOREIGN_KEY_CHECKS = 1');
      
      // 初始化数据
      await initializeData(connection);
    } else {
      // 检查是否需要初始化数据
      logger.info('数据库结构完整，检查是否需要初始化数据...');
      
      // 检查是否有数据
      const hasData = await checkIfDataExists(connection);
      if (!hasData) {
        logger.info('数据库表结构正确但缺少初始数据，执行数据初始化');
        await initializeData(connection);
      } else {
        logger.info('数据库结构和数据都已完整，无需任何操作');
      }
    }

    logger.info('数据库初始化完成');
  } catch (error) {
    logger.error('数据库初始化失败:', error);
    throw error;
  } finally {
    if (connection) {
      logger.info('关闭数据库连接');
      await connection.end();
    }
  }
}

/* *******************
 * 检查是否有数据
 ******************* */
async function checkIfDataExists(connection) {
  try {
    const sqlPath = path.join(__dirname, 'init.sql');
    const content = await fs.readFile(sqlPath, 'utf8');
    
    // 清理SQL内容
    const cleanedContent = content
      .replace(/--.*$/gm, '') // 移除单行注释
      .replace(/\/\*[\s\S]*?\*\//g, ''); // 移除多行注释
    
    // 匹配所有CREATE TABLE语句获取表名
    const tableRegex = /CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`(\w+)`/gi;
    const tables = [];
    let match;
    
    while ((match = tableRegex.exec(cleanedContent)) !== null) {
      tables.push(match[1]);
    }
    
    if (tables.length === 0) {
      logger.warn('SQL文件中未找到任何表定义');
      return false;
    }
    
    // 检查关键表是否有数据
    for (const table of tables) {
      const [rows] = await connection.query(`SELECT COUNT(*) as count FROM ${mysql.escapeId(table)}`);
      if (rows[0].count === 0) {
        logger.info(`表 ${table} 没有数据`);
        return false;
      }
    }
    
    logger.info('所有表都有数据');
    return true;
  } catch (error) {
    logger.error('检查数据时出错:', error);
    return false; // 出错时保守处理，认为没有数据
  }
}

export default initDatabase;